package com.hama.iotrust.common.util;

import java.io.IOException;
import java.net.SocketException;
import java.nio.file.Files;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.commons.net.ftp.FTPClient;
import org.apache.commons.net.ftp.FTPFile;
import org.apache.commons.net.ftp.FTPReply;

import com.hama.iotrust.common.ServiceException;

/**
 * @author 123456
 *
 */
public class PicClean {

	// 独立功能,Test.java 运行

	/* 从服务器获取图片 */
	private String ftpIp = "192.168.2.228";
	// 上传端口
	private String port = "21";
	private String userName = "yixun";
	private String password = "yixun0026";
	// 下载端口
	private String ngixPort = "8081";

	
	
	
	/**
	 * @param table_name 	表名
	 * @param module     	模块名
	 * @param column1    	列名1
	 * @param column2		列名2(如果没有则写:"")
	 */
	public void clean(String table_name,String module,String column1,String column2){
		FTPClient ftp = new FTPClient();
		//String sql = "SELECT img FROM " + table_name;
		//拼接sql语句
		String sql="";
		if("".equals(column2)){
			sql="SELECT "+ column1+" FROM " + table_name;
		}else{
			sql="SELECT "+ column1+", "+column2+" FROM " + table_name;
		}
		
		try {
			ftp.connect(ftpIp, Integer.parseInt(port));
			boolean loginResult = ftp.login(userName, password);
			int reply = ftp.getReplyCode();
			if (!FTPReply.isPositiveCompletion(reply)) {
				ftp.disconnect();
			}
			ArrayList<String> list_database = new ArrayList<String>();
			ArrayList<String> list_ngix = new ArrayList<String>();
			ArrayList<String> list_delete = new ArrayList<String>();
			
			//数据库连接配置
			String url = "jdbc:mysql://192.168.2.228:3306/yixun?autoReconnect=true&amp;useUnicode=true&amp;characterEncoding=UTF-8";
			String username = "dev";
			String password = "dev0026";
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Connection conn = DriverManager.getConnection(url, username,
					password);
			Statement stmt = conn.createStatement();
			ResultSet rs = null;
			//228上数据
			FTPFile[] files = ftp.listFiles(module);
			for (FTPFile ftpFile : files) {
				if(ftpFile.isFile()){
					list_ngix.add(ftpFile.getName());
				}
				
			}
			
			// 查询数据库
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				list_database.add(rs.getString(1));
				if(!rs.getString(2).isEmpty()){
					String strs = rs.getString(2);
					if (strs.contains(",")) {
						String[] temp = strs.split(",");
						for (String st : temp) {
							list_database.add(st);
						}
					}
				}
				
			}
			
			boolean flag=false;
			//执行操作
			Iterator<String> iter=list_ngix.iterator();
			while(iter.hasNext()){
				String str_ngix=iter.next();
				for (String str_database : list_database) {
					if(str_database.contains(str_ngix)){
						flag=true;
						break;
					}else{
						flag=false;
					}
				}
				
				if(!flag){
					//ftp.deleteFile(module+"/"+str_ngix);
					list_delete.add(str_ngix);
				}
			}

			//删除
			for (String  str_del : list_delete) {
				ftp.deleteFile(module+"/"+str_del);
			}
			rs.close();
			conn.close();
		} catch (Exception e) {
			throw new ServiceException(e.getMessage(), e);
		} finally {
			try {
				ftp.logout();
			} catch (IOException e) {
				throw new ServiceException(e.getMessage(), e);
			}
		}
		
		
		
		
		
		
	}

	// ftp.getLocalAddress();//本机ip ： /192.168.2.137

	// System.out.println(reply);
	// System.out.println(ftp.getLocalAddress());// /192.168.2.137
	// System.out.println(ftp.getClass()); // class
	// org.apache.commons.net.ftp.FTPClient
	// System.out.println(ftp.acct(ftpIp)); // 202

}
